// sql_commands.cpp
//

#if _MSC_VER > 1000
#pragma warning( disable :  4511 4512 4786)
#endif
#include <string>
#include <iostream>
#include <fstream>
#include <vector>
#include <algorithm>
#include <assert.h>
//#include <boost/array_traits.hpp>

#include "components/pg_db_cursor.h"
#include "components/libpq++/libpq++.h"

#include "sql_commands.h"
#include  "components/stlhelper.h"

using std::string;
using std::vector;
using std::ostream;
using std::unique;
//include this after using because it is really part of the cpp file not a shared header
#include "sql_commands_inc.h"
namespace{
	vector<vector<string> > SQL_descriptions;
	vector<vector<string> > SQL_commands;
}


//_______________________________________________________________________________________


//		PrepareSQLCommands

//					replace the dummy values in the SQL commands with the filenames
//					specified on the command line
//		[9/27/2001]

//_______________________________________________________________________________________
void PrepareSQLCommands(t_pg_cursor& pg_db, const string& db_origin)
{
	// make vectors of SQL commands: tedious and inelegant but safe(r)
	for (unsigned i = 0; i < eSQL_CMDS_LAST; ++i)
		SQL_descriptions.push_back(vector<string>());
	SQL_descriptions[0].insert(SQL_descriptions[0].begin(),
								SQL_descriptions_stage0,
								SQL_descriptions_stage0 +
								sizeof(SQL_descriptions_stage0) / sizeof(string));
	SQL_descriptions[1].insert(SQL_descriptions[1].begin(),
								SQL_descriptions_stage1,
								SQL_descriptions_stage1 +
								sizeof(SQL_descriptions_stage1) / sizeof(string));
	SQL_descriptions[2].insert(SQL_descriptions[2].begin(),
								SQL_descriptions_stage2,
								SQL_descriptions_stage2 +
								sizeof(SQL_descriptions_stage2) / sizeof(string));
	for (unsigned i = 0; i < eSQL_CMDS_LAST; ++i)
		SQL_commands.push_back(vector<string>());
	SQL_commands[0].insert(SQL_commands[0].begin(),
								SQL_commands_stage0,
								SQL_commands_stage0 +
								sizeof(SQL_commands_stage0) / sizeof(string));
	SQL_commands[1].insert(SQL_commands[1].begin(),
								SQL_commands_stage1,
								SQL_commands_stage1 +
								sizeof(SQL_commands_stage1) / sizeof(string));
	SQL_commands[2].insert(SQL_commands[2].begin(),
								SQL_commands_stage2,
								SQL_commands_stage2 +
								sizeof(SQL_commands_stage2) / sizeof(string));

//sanity checks
#ifndef NDEBUG
    for (unsigned i = 0; i < eSQL_CMDS_LAST; ++i)
	{
    	assert(SQL_commands[i].size() == SQL_descriptions[i].size());
	    assert(SQL_commands[i].size() != 0);
	    assert(SQL_commands[i].size() < 20);
    }
#endif


    for (unsigned i = 0; i < eSQL_CMDS_LAST; ++i)
	    for (unsigned j = 0; j < SQL_commands[i].size(); ++j)
		{
			// remove aligning spaces
			remove_specified_duplicates(SQL_commands[i][j], ' ');
		
			// replace the dummy values with the actual file names
			str_search_and_replace(SQL_commands[i][j], "db_origin_tag", db_origin);
	    }
}

//_______________________________________________________________________________________


//		ExecutePandaSQL

//			Call the specified sets of SQL Commands (from 1 to 4)
//					specified on the command line
//		[9/27/2001]

//_______________________________________________________________________________________
bool ExecutePandaSQL(t_pg_cursor& pg_db, eSQL_CMDS stage, ostream& VERBOSE, ostream& CERR)
{
	//vector<int>& results,
	//results.clear();
    for (unsigned i = 0; i < SQL_commands[stage].size(); ++i)
	{
//		std::cerr << '[' << SQL_commands[stage][i] << "]\n";
		ExecStatusType stat = pg_db.Exec(SQL_commands[stage][i].c_str());
		if (stat !=  PGRES_COMMAND_OK && stat != PGRES_TUPLES_OK)
//	    if(!pg_db.ExecCommandOk(SQL_commands[stage][i].c_str()))
		{
			
			CERR		<< "ERROR!! PostgreSQL error (in stage " << stage << "): \n"
						<< pg_db.ErrorMessage()
			            << "Failed to " << SQL_descriptions[stage][i] << "\n"
						<< "SQL [ " << SQL_commands[stage][i] << " ]\n"
						<< stat << "\n";
			return false;
		}
		if (stat == PGRES_TUPLES_OK)
		{
			int curr_batch_size = pg_db.Tuples();
			for (int i = 0; i < curr_batch_size; ++i)
				VERBOSE << "\n" << pg_db.GetValue(i, 0);
			VERBOSE << "\n";

		}
		VERBOSE << "..";
//		results.push_back(pg_db.CmdTuples());
	}	
    return true;
}




//_______________________________________________________________________________________


//		CompleteSQL

//			Commit the changes to the database and cleanup/optimize the tables
//		[9/27/2001]


//_______________________________________________________________________________________
void CompleteSQL(t_pg_cursor& pg_db, bool vacuum, ostream& CERR)
{
    // commit transaction	  b
    pg_db.Commit();

	if (!vacuum)
		return;
    if( !pg_db.ExecCommandOk("ANALYZE sequences") ||
		!pg_db.ExecCommandOk("ANALYZE accessions"))
        CERR	<< "ERROR!! Failed to clean and analyse the new database tables\n"
				<< pg_db.ErrorMessage();

    return;
}


//_______________________________________________________________________________________


//		DropIndices

//			Drop all indices for an indicated table and returns the SQL commands
//				needed to recreate the indices

//_______________________________________________________________________________________
bool DropIndices(t_pg_cursor& pg_db, const string& table,
					vector<string>& vec_indexdef, ostream& CERR)
{
	// do not clear vec_indexdef!!!

	// retrieve index names
	string SQL_cmd =	"SELECT indexname, indexdef "
						"FROM pg_indexes WHERE tablename = '"	+
						table									+
						"'";
	if(!pg_db.ExecTuplesOk(SQL_cmd.c_str()))
	{
		CERR		<< "ERROR!! PostgreSQL error\n"
					<< "Could not retrieve index names for the '" << table << "' table\n"
					<< pg_db.ErrorMessage()
					<< "SQL [ " << SQL_cmd << " ]\n";
		return false;
	}

	// save sql index creation command
	vector<string> vec_indexname;
	for (int i = 0; i < pg_db.Tuples(); ++i)
	{
		vec_indexname.push_back(pg_db.GetValue(i, 0));
		vec_indexdef.push_back(pg_db.GetValue(i, 1));
	}

	// drop each index
	for (unsigned i = 0; i < vec_indexname.size(); ++i)
	{
		SQL_cmd = "DROP INDEX " + vec_indexname[i];
		if( !pg_db.ExecCommandOk(SQL_cmd.c_str()))
		{
			CERR		<< "ERROR!! PostgreSQL error\n"
						<< "Could not remove the index '" << vec_indexname[i]
						<< "' for the '" << table << "' table\n"
						<< pg_db.ErrorMessage()
						<< "SQL [ " << SQL_cmd << " ]\n";
			return false;
		}
	}
	return true;
}

//_______________________________________________________________________________________


//		Run SQL Commands

//_______________________________________________________________________________________
bool ExecuteSQL(t_pg_cursor& pg_db, string err_description,
					vector<string>& SQL_commands, ostream& CERR)
{
	for (unsigned i = 0; i < SQL_commands.size(); ++i)
	{
		if( !pg_db.ExecCommandOk(SQL_commands[i].c_str()))
		{
			CERR		<< "ERROR!! PostgreSQL error\n"
						<< err_description
						<< pg_db.ErrorMessage()
						<< "SQL [ " << SQL_commands[i] << " ]\n";
			return false;

		}
	}
	return true;
}
